package com.zonghengdb;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import com.Model.CrawlListInfo;
import com.Model.NovelChapterModel;
import com.Model.NovelInfoModel;
import com.Model.NovelReadMode;
import com.Util.ParseMD5;
import com.Util.jsonUtil;
import com.db.manager.DBServer;

public class ZongHengDB {
	private static final String POOLNAME = "proxool.zongheng";
	
	/**
	 * 获取更新列表页信息list
	 * @return 更新列表页信息的list
	 */
	public List<CrawlListInfo> getCrawlListInfos(){
		List<CrawlListInfo> infos = new ArrayList<CrawlListInfo>();
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			String sql = "select * from crawllist where state = '1'";
			ResultSet rs = dbServer.select(sql);
			while(rs.next()){
				CrawlListInfo info = new CrawlListInfo();
				infos.add(info);
				info.setUrl(rs.getString("url"));
				info.setInfo(rs.getString("info"));
				info.setFrequency(rs.getString("frequency"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
		return infos;
	}
	
	/**
	 * 随机获取简介页url
	 * @param state 状态标志
	 * @return 简介与的url
	 */
	public String getRankIntroPageUrl(int state) {
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			String sql = "select * from novelinfo where state = '"+state+"' order by rand() limit 1";
			ResultSet rs = dbServer.select(sql);
			while(rs.next()){
				return rs.getString("url");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
		return null;
	}
	/**
	 * 随机获取小说章节页信息
	 * @param state
	 * @return
	 */
	public NovelChapterModel getRandChapter(int state) {
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			String sql = "select * from novelchapter where state = '"+state+"' order by rand() limit 1";
			ResultSet rs = dbServer.select(sql);
			while(rs.next()){
				NovelChapterModel bean =new NovelChapterModel(); 
				bean.setId(rs.getString("id"));
				bean.setUrl(rs.getString("url"));
				bean.setChapterId(rs.getInt("chapterid"));
				bean.setChapterTime(rs.getLong("chaptertime"));
				return bean;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
		return null;
	}
	
	
	
	//保存更新列表页的URLS
	public void saveInfoUrls(List<String> urls){
		if(urls == null || urls.size() < 1){
			return;
		}
		for(String url : urls){
			String id = ParseMD5.parseStrToMD5(url);
			if(hasNovelInfo(id)){
				updateInfoState(id, 1);
			}else {
				insertInfoUrl(id, url);
			}
			
		}
	}
	
	//小说简介信息的更新
	public void updateNovelInfo(NovelInfoModel bean){
		if(bean == null){
			return ;
		}
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			HashMap<Integer, Object> params = new HashMap<Integer, Object>();
			int i = 1;
			params.put(i++, bean.getName());
			params.put(i++, bean.getAuthor());
			params.put(i++, bean.getDesc());
			params.put(i++, bean.getType());
			params.put(i++, bean.getLastChapter());
			params.put(i++, bean.getChapterListUrl());
			params.put(i++, bean.getWordCount());
			params.put(i++, bean.getKeyWords());
			params.put(i++, System.currentTimeMillis());
			params.put(i++, "0");
			
			String columns = "name,author,description,type,lastchapter,chapterlisturl,wordcount,keywords,updatetime,state";
			String condition = "where id = '" + bean.getId() + "'";
			dbServer.update("novelinfo", columns, condition, params);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
	}
	/**
	 * 保存小说章节页信息
	 * @param beans
	 * @param novelId
	 */
	public void saveNovelChapter(List<NovelChapterModel> beans,String novelId){
		if(beans == null){
			return;
		}
		for(NovelChapterModel bean:beans){
			if(!havaNovelChapter(bean.getId())){
				insertNovelChapter(bean, novelId);
			}
		}	
	}
	/**
	 * 保存小说阅读页信息
	 * @param bean
	 */
	public void saveNovelRead(NovelReadMode bean){
		if(bean == null){
			return;
		}
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			if(haveNovelRead(bean.getId())){
				
				return;
			}
			HashMap<Integer, Object> params = new HashMap<Integer, Object>();
			int i = 1;
			params.put(i++, bean.getId());
			params.put(i++, bean.getUrl());
			params.put(i++, bean.getTitle());
			params.put(i++, bean.getWordCount());
			params.put(i++, bean.getChapterId());
			params.put(i++, bean.getContent());
	
			params.put(i++, bean.getChapterTime());
			params.put(i++, System.currentTimeMillis());
			params.put(i++, System.currentTimeMillis());

			String columns = "id,url,title,wordcount,chapterid,content,chaptertime,createtime,updatetime";
			dbServer.insert("novelchapterdetail", columns, params);

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
		
	}
	/**
	 * 阅读页id是否已经存在
	 * @param id
	 * @return
	 */
	private boolean haveNovelRead(String id) {
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			String sql = "select sum(1) as count from novelchapterdetail where id= '" + id + "'";
			ResultSet rs = dbServer.select(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				return count > 0;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
		return true;
	}
	
	
	
	//数据库中是否存在该记录 ---章节id
	private boolean havaNovelChapter(String id){
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			String sql = "select sum(1) as count from novelchapter where id= '" + id + "'";
			ResultSet rs = dbServer.select(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				return count > 0;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
		return true;
	}
	
	
	private void insertNovelChapter(NovelChapterModel bean,String novelId){
		if(bean == null){
			return ;
		}
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			HashMap<Integer, Object> params = new HashMap<Integer, Object>();
			int i = 1;
			params.put(i++, bean.getId());
			params.put(i++, novelId);
			params.put(i++, bean.getUrl());
			params.put(i++, bean.getTitle());
			params.put(i++, bean.getWordCount());
			params.put(i++, bean.getChapterId());
			params.put(i++, bean.getChapterTime());
			params.put(i++, System.currentTimeMillis());
			params.put(i++, "1");
			
			
			String columns = "id,novelid,url,title,wordcount,chapterid,chaptertime,createtime,state";
			dbServer.insert("novelchapter", columns, params);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
	}
	
	public void  updateChapterState(String id,int state) {
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			String sql = "update novelchapter set state='" + state + "' where id = '" + id + "'";
			dbServer.update(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
	}
	
	
	
	
	
	//修改状态的值
	private void updateInfoState(String id,int state){
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			String sql = "update novelinfo set state='" + state + "' where id = '" + id + "'";
			dbServer.update(sql);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
	}
	//url信息是否在数据库中存在
	private boolean hasNovelInfo(String id){
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			String sql = "select sum(1) as count from novelinfo where id= '" + id + "'";
			ResultSet rs = dbServer.select(sql);
			if(rs.next()){
				int count = rs.getInt("count");
				return count > 0;
			}
			return false;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
		return true;
	}
	
	//插入一条URL
	private void insertInfoUrl(String id,String url){
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			HashMap<Integer, Object> params = new HashMap<Integer, Object>();
			int i = 1;
			params.put(i++, id);
			params.put(i++, url);
			long now = System.currentTimeMillis();
			params.put(i++, now);
			params.put(i++, now);
			params.put(i++, 1);
			dbServer.insert("novelinfo","id,url,createtime,updatetime,state",params);

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
	}
	
	
	
	public void method(){
		DBServer dbServer = new DBServer(POOLNAME);
		try {
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			dbServer.close();
		}
	}
	
	
	
	
	public static void main(String[] args) {
		ZongHengDB db = new ZongHengDB();
		System.out.println(db.getRankIntroPageUrl(1));
		System.out.println(jsonUtil.parseJson(db.getRandChapter(1)));
		
	}
	
}
